package myauctioneer.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import myauctioneer.App;
import myauctioneer.dto.Article;

public class ArticleDAO implements ArticleDAOI {

	private PreparedStatement saveStmt;
	private PreparedStatement saveStmtUpdate;
	private PreparedStatement loadStmt0;
	private PreparedStatement loadStmt1;
	private PreparedStatement loadStmt2;
	private PreparedStatement deleteStmt;

	public ArticleDAO() {
		try {
			saveStmt = App
					.getDb()
					.prepareStatement(
//old							"INSERT INTO item(brand, family_line, model, condition, description, created_at, valid_until, category, price, sale_type, final_bid, customer_id) "
//									+ "VALUES (?, ?, ?, ?, ?, to_date(?, 'dd-mm-yyyy HH24:MI'), to_date(?, 'dd-mm-yyyy HH24:MI'), ?, ?, ?, ?, ?)");
							"INSERT INTO item(brand, family_line, model, condition, description, created_at, valid_until, category, price, sale_type, customer_id) "
									+ "VALUES (?, ?, ?, ?, ?, to_date(?, 'dd-mm-yyyy HH24:MI'), to_date(?, 'dd-mm-yyyy HH24:MI'), ?, ?, ?, ?)"); //simon, new

			saveStmtUpdate = App.getDb().prepareStatement(
				"UPDATE item SET " +
				"brand = ?, " +
				"family_line = ?, " +
				"model = ?, " +
				"condition = ?, " +
				"description = ?, " +
				"created_at = to_date(?, 'yyyy-mm-dd HH24:MI:SS'), " +
				"valid_until = to_date(?, 'yyyy-mm-dd HH24:MI:SS'), " +
				"category = ?, " +
				"price = ?, " +
				"sale_type = ?, " +
				"final_bid = ?, " +
				"customer_id = ? " +
				"WHERE item_id = ?"
			);

			loadStmt0 = App.getDb().prepareStatement(
					"SELECT * FROM item WHERE item_id = ?");

			loadStmt1 = App.getDb().prepareStatement(
					"SELECT * FROM item WHERE customer_id = ?");

			loadStmt2 = App
					.getDb()
					.prepareStatement(
							"SELECT i.item_id, i.brand, i.family_line, i.model, i.condition, i.description, i.created_at, i.valid_until, i.category, i.price, i.sale_type, i.final_bid FROM item i INNER JOIN bid b ON i.final_bid = b.bid_id INNER JOIN customer c ON b.customer_id = c.customer_id WHERE (SELECT CURRENT_TIMESTAMP FROM DUAL) > TO_TIMESTAMP(TO_CHAR(i.valid_until,'dd-mm-yyyy HH24:MI'),'dd-mm-yyyy HH24:MI') AND c.customer_id = ?");

			deleteStmt = App.getDb().prepareStatement(
					"DELETE FROM item WHERE customer_id = ?");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public void saveArticle(Article a) {
		try {
			PreparedStatement stmt = saveStmt;
			
			if(a.getArticleId() > 0) {
				stmt = saveStmtUpdate;
				stmt.setInt(13, a.getArticleId());

				App.getLogger().info("Updating article: " + a.getArticleId());
			}

			stmt.setString(1, a.getBrand());
			stmt.setString(2, a.getFamilyLine());
			stmt.setString(3, a.getModel());
			stmt.setString(4, a.getCondition());
			stmt.setString(5, a.getDescription());
			stmt.setString(6, a.getCreatedAt());
			stmt.setString(7, a.getValidUntil());
			stmt.setString(8, a.getCategory());
			stmt.setDouble(9, a.getPrice());
			stmt.setString(10, a.getSaleType());
//old			stmt.setInt(11, a.getFinalBid());
//			stmt.setInt(12, a.getUserId());
			stmt.setInt(11, a.getUserId());//simon, new

			if(stmt.executeUpdate() == 0)
				throw new SQLException("Cannot save article.");
		}
		catch (SQLException e) {
			App.getLogger().log(java.util.logging.Level.WARNING, e.getMessage(), e);
			e.printStackTrace();
		}
	}

	@Override
	public Article loadArticleByUserId(int userId) {
		Article a = new Article();
		try {
			loadStmt1.setInt(1, userId);
			ResultSet result = loadStmt1.executeQuery();

			if (result.next())
				a = this.load(result);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return a;
	}

	@Override
	public Article loadArticleByArticleId(int articleId) {
		Article a = null;
		try {
			loadStmt0.setInt(1, articleId);
			ResultSet result = loadStmt0.executeQuery();

			if (result.next())
				a = this.load(result);
		} catch (SQLException e) {
			App.getLogger().info(e.getMessage());
			e.printStackTrace();
		}

		return a;
	}

	@Override
	public ArrayList<Article> loadArticlesByUserId(int userId) {
		ArrayList<Article> aAL = new ArrayList<Article>();
		try {
			loadStmt1.setInt(1, userId);
			ResultSet result = loadStmt1.executeQuery();

			while (result.next())
				aAL.add(this.load(result));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return aAL;
	}

	protected Article load(ResultSet result) throws SQLException {
		Article a = new Article();

		a.setArticleId(result.getInt("item_id"));
		a.setBrand(result.getString("brand"));
		a.setFamilyLine(result.getString("family_line"));
		a.setModel(result.getString("model"));
		a.setCondition(result.getString("condition"));
		a.setDescription(result.getString("description"));
		a.setCreatedAt(result.getString("created_at"));
		a.setValidUntil(result.getString("valid_until"));
		a.setCategory(result.getString("category"));
		a.setPrice(result.getDouble("price"));
		a.setSaleType(result.getString("sale_type"));
		a.setFinalBid(result.getInt("final_bid"));
		a.setUserId(result.getInt("customer_id"));

		return a;
	}

	@Override
	public ArrayList<Article> loadArticlesByCategory(String category) {
		ArrayList<Article> aAL = new ArrayList<Article>();
		try {
			loadStmt1.setString(1, category);
			ResultSet result = loadStmt1.executeQuery();

			while (result.next())
				aAL.add(this.load(result));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return aAL;
	}

	/**
	 * Query articles by arbitrary parameters. Currently only limited set of
	 * parameters implemented.
	 */
	public ArrayList<Article> query(java.util.Map<String, String> q) {
		ArrayList<Article> aAL = new ArrayList<Article>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String param = null;

		try {
			StringBuffer sql = new StringBuffer();
			ArrayList<String> sqlMap = new ArrayList<String>();

			sql.append("SELECT * FROM item WHERE 1=1");

			param = q.get("query");

			if (param != null && !param.equals("")) {
				// Case insensitive matching
				sql.append(" AND (lower(brand) LIKE lower(?) OR "
						+ "lower(family_line) LIKE lower(?) OR "
						+ "lower(model) LIKE lower(?))");
				param = "%" + param + "%";
				sqlMap.add(param);
				sqlMap.add(param);
				sqlMap.add(param);
			}

			param = q.get("category");

			if (param != null && !param.equals("")) {
				sql.append(" AND category = ?");
				sqlMap.add(param);
			}

			App.getLogger().info("Query: " + sql.toString());
			ps = App.getDb().prepareStatement(sql.toString());

			for (int i = 0; i < sqlMap.size(); ++i)
				ps.setString(i + 1, sqlMap.get(i));

			rs = ps.executeQuery();

			while (rs.next())
				aAL.add(this.load(rs));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (ps != null)
				try {
					ps.close();
				} catch (Throwable e) {
				}
			if (rs != null)
				try {
					rs.close();
				} catch (Throwable e) {
				}
		}

		return aAL;
	}

	@Override
	public boolean deleteArticleById(int userId) {
		try {
			deleteStmt.setInt(1, userId);
			if (deleteStmt.executeUpdate() == 1) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public ArrayList<Article> loadArticlesByUserIdWithInvalidDate(int userId) {
		ArrayList<Article> aAL = new ArrayList<Article>();
		try {
			loadStmt2.setInt(1, userId);
			ResultSet result = loadStmt2.executeQuery();

			while (result.next())
				aAL.add(this.loadArticleByArticleId(result.getInt("item_id")));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return aAL;
	}
}
